import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from SKO.AbstractDPJob import AbstractDPJob
class Predict_ranliaobiJob(AbstractDPJob):
    def __init__(self,
                 p_bf_no=None,p_avg_iron_temp=None,p_avg_c_s_value=None,p_compute_slag_rate=None,p_avg_s_value=None,p_jiaotan_s=None,p_penchuimei_s=None):

        super(Predict_ranliaobiJob, self).__init__()
        self.bf_no = p_bf_no
        self.avg_iron_temp = p_avg_iron_temp
        self.avg_c_s_value = p_avg_c_s_value
        self.compute_slag_rate = p_compute_slag_rate
        self.avg_s_value = p_avg_s_value
        self.jiaotan_s = p_jiaotan_s
        self.penchuimei_s = p_penchuimei_s
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):
        super(Predict_ranliaobiJob, self).do_execute()
        #预测铁水硫接口传入参数
        bf_no = self.bf_no
        avg_iron_temp = self.avg_iron_temp
        avg_c_s_value = self.avg_c_s_value
        compute_slag_rate = self.compute_slag_rate
        avg_s_value = self.avg_s_value
        jiaotan_s = self.jiaotan_s
        penchuimei_s = self.penchuimei_s
        start = datetime.datetime.now()
        df_coef = pd.read_excel('铁水硫模型参数2.xlsx')
        df_coef.columns = df_coef.columns.str.upper()
        gamma = df_coef.loc[0]['GAMMA']
        neighbors_num = df_coef.loc[0]['NEIGHBORS_NUM']
        guiyihua = df_coef.loc[0]['GUIYIHUA']
        modelname = df_coef.loc[0]['MODELNAME']
        # 铁水硫
        xlsx_name = 'D:/repos/sicost/fe_s_' + str(bf_no) + '.xlsx'
        df1 = pd.read_excel(xlsx_name)
        df1.columns = df1.columns.str.upper()
        df1['PROD_DATE'] = df1['PROD_DATE'].astype(str)
        df1.drop(['UNIT_NO'], axis=1, inplace=True)
        # 燃料比
        xlsx_name = 'D:/repos/sicost/ranliaobi_' + str(bf_no) + '.xlsx'
        df2 = pd.read_excel(xlsx_name)
        df2.columns = df2.columns.str.upper()
        df2['PROD_DATE'] = df2['PROD_DATE'].astype(str)
        df2.drop(['UNIT_NO'], axis=1, inplace=True)
        df2.drop(['PROC_UNIT'], axis=1, inplace=True)
        # 喷吹煤硫
        xlsx_name = 'D:/repos/sicost/penchuimei_' + str(bf_no) + '.xlsx'
        df3 = pd.read_excel(xlsx_name)
        df3.columns = df3.columns.str.upper()
        df3['MAT_SAMPLE_DATE'] = df3['MAT_SAMPLE_DATE'].astype(str)
        df3.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
        # 焦炭硫
        xlsx_name = 'D:/repos/sicost/jiaotan.xlsx'
        df4 = pd.read_excel(xlsx_name)
        df4.columns = df4.columns.str.upper()
        df4['MAT_SAMPLE_DATE'] = df4['MAT_SAMPLE_DATE'].astype(str)
        df4.rename(columns={'MAT_SAMPLE_DATE': 'PROD_DATE'}, inplace=True)
        # 数据关联
        v = ['PROD_DATE']
        df_12 = pd.merge(df1, df2, on=v, how='left')
        df_123 = pd.merge(df_12, df3, on=v, how='left')
        df_1234 = pd.merge(df_123, df4, on=v, how='left')

        # 数据清洗
        # 去除0和nan
        def clean_data(df, gamma):
            column_name_list = df.columns.tolist()
            column_name_list.remove('PROD_DATE')
            column_name_num = len(column_name_list)
            clean_str_start = 'df_new = df['
            clean_str_end = ']'
            ldict1 = {}
            for i in range(0, column_name_num):
                print(i)
                print(column_name_list[i])
                column_name_tmp = column_name_list[i]
                exec("q1_{} = df['{}'].quantile(0.25)".format(i, column_name_tmp), locals(), ldict1)
                exec("q3_{} = df['{}'].quantile(0.75)".format(i, column_name_tmp), locals(), ldict1)
                exec("iqr_val_{} = q3_{} - q1_{}".format(i, i, i), locals(), ldict1)
                exec(
                    '''clean_str1 = "(df['{}'] >= ldict1['q1_{}'] - gamma * ldict1['iqr_val_{}'])"'''.format(
                        column_name_tmp, i,
                        i), locals(),
                    ldict1)
                exec('''clean_str2 = "(df['{}'] < ldict1['q3_{}'] + gamma * ldict1['iqr_val_{}'])"'''.format(
                    column_name_tmp, i,
                    i), locals(),
                     ldict1)
                exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                clean_str1 = ldict1["clean_str1"]
                clean_str2 = ldict1["clean_str2"]
                clean_str3 = ldict1["clean_str3"]
                if i == 0:
                    clean_str_start = clean_str_start + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
                else:
                    clean_str_start = clean_str_start + ' & ' + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
            clean_str = clean_str_start + clean_str_end
            print(clean_str)
            exec(clean_str, locals(), ldict1)
            df_new = ldict1["df_new"]
            df_new = df_new.reset_index(drop=True)
            return df_new

        df_clean_nan = df_1234.dropna()
        df_clean_0 = df_clean_nan[df_clean_nan.ne(0).all(axis=1)]

        # df0_clean = clean_data(df_1234, gamma)
        # df0_clean2 = clean_data(df_cleaned, gamma)
        df_clean_liqun = clean_data(df_clean_0, gamma)
        df_clean_liqun.drop(['SUM_CACULATE_IRON_WGT'], axis=1, inplace=True)
        df0 = df_clean_liqun.copy()
        df0['ZONGJIAOBI'] = df0['JIAOBI'] + df0['XIAOJIAOBI']
        df0['JIAOTAN_DAIRULIU'] = df0['JIAOTAN_S'] * df0['ZONGJIAOBI']
        df0['PENCHUIMEI_DAIRULIU'] = df0['PENCHUIMEI_S'] * df0['MEIBI']
        df0['KUANGSHI_DAIRULIU'] = df0['COMPUTE_FILL_S_VALUE'] - df0['JIAOTAN_DAIRULIU'] - df0['PENCHUIMEI_DAIRULIU']
        q1 = df0['KUANGSHI_DAIRULIU'].quantile(0.25)
        q3 = df0['KUANGSHI_DAIRULIU'].quantile(0.75)
        iqr_val = q3 - q1
        df0_new = df0[
            (df0['KUANGSHI_DAIRULIU'] < q3 + gamma * iqr_val) & (df0['KUANGSHI_DAIRULIU'] >= q1 - gamma * iqr_val) & (
                        df0['KUANGSHI_DAIRULIU'] > 0)]
        df0_new = df0_new.reset_index(drop=True)


        df04_new = df0_new.copy()
        df04_new.drop(['PROD_DATE'], axis=1, inplace=True)
        # df04_new.drop(['AVG_S_VALUE'], axis=1, inplace=True)
        # df04_new.drop(['AVG_C_S_VALUE'], axis=1, inplace=True)
        df04_new.drop(['COMPUTE_FILL_S_VALUE'], axis=1, inplace=True)
        df04_new.drop(['JIAOBI'], axis=1, inplace=True)
        df04_new.drop(['XIAOJIAOBI'], axis=1, inplace=True)
        df04_new.drop(['PENCHUIMEI_S'], axis=1, inplace=True)
        df04_new.drop(['JIAOTAN_S'], axis=1, inplace=True)
        # df04_new.drop(['JIAOTAN_DAIRULIU'], axis=1, inplace=True)
        # df04_new.drop(['PENCHUIMEI_DAIRULIU'], axis=1, inplace=True)
        df04_new.drop(['ZONGJIAOBI'], axis=1, inplace=True)
        df04_new.drop(['MEIBI'], axis=1, inplace=True)
        df04_new.drop(['KUANGSHI_DAIRULIU'], axis=1, inplace=True)
        df0_train_clean = df04_new.copy()
        # df0_train_clean_Y = df0_train_clean[['ZONGJIAOBI', 'MEIBI']]
        df0_train_clean_Y = df0_train_clean[['JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU']]

        # 将需要预测的数据拼接上
        dict_input = {}
        dict_input['AVG_IRON_TEMP'] = avg_iron_temp
        dict_input['AVG_S_VALUE'] = avg_s_value
        dict_input['AVG_C_S_VALUE'] = avg_c_s_value
        dict_input['COMPUTE_SLAG_RATE'] = compute_slag_rate

        new_row = pd.Series(dict_input)
        df0_train_clean = df0_train_clean.append(new_row, ignore_index=True)
        # df0_train_clean_X = df0_train_clean.drop(labels=['ZONGJIAOBI', 'MEIBI'], axis=1, inplace=False)
        df0_train_clean_X = df0_train_clean.drop(labels=['JIAOTAN_DAIRULIU', 'PENCHUIMEI_DAIRULIU'], axis=1, inplace=False)

        if guiyihua == 'StandardScaler':
            transfer = StandardScaler()
        else:
            transfer = MinMaxScaler()
        X_trans = transfer.fit_transform(df0_train_clean_X)
        df_X_trans = pd.DataFrame(X_trans)
        # 求欧式距离
        input_data_X = df_X_trans.iloc[-1].tolist()
        input_X = df_X_trans.iloc[-1:]
        df_X_new = df_X_trans.iloc[:-1]
        df_X_new_copy = df_X_new.copy()
        df_X_new_copy['distance'] = df_X_new_copy.apply(lambda row: distance.euclidean(row, input_data_X), axis=1)
        # df_X_new_copy['y1'] = df0_train_clean_Y['ZONGJIAOBI']
        # df_X_new_copy['y2'] = df0_train_clean_Y['MEIBI']
        df_X_new_copy['y1'] = df0_train_clean_Y['JIAOTAN_DAIRULIU']
        df_X_new_copy['y2'] = df0_train_clean_Y['PENCHUIMEI_DAIRULIU']
        df_sorted = df_X_new_copy.sort_values(by='distance')
        df_sorted = df_sorted.reset_index(drop=True)
        # 找邻居回归出预测结果
        df_head = df_sorted.head(neighbors_num)
        if modelname == 'Linear':
            model = LinearRegression()
        elif modelname == 'XGB':
            model = xgb.XGBRegressor()
        elif modelname == 'RandomForest':
            model = RandomForestRegressor()
        elif modelname == 'GradientBoosting':
            model = GradientBoostingRegressor()
        elif modelname == 'AdaBoost':
            model = AdaBoostRegressor()
        elif modelname == 'Bagging':
            model = BaggingRegressor()
        elif modelname == 'DecisionTree':
            model = DecisionTreeRegressor()
        elif modelname == 'ExtraTrees':
            model = ExtraTreesRegressor()
        elif modelname == 'KNeighbors':
            model = KNeighborsRegressor()
        elif modelname == 'Lasso':
            model = Lasso(alpha=0.1)
        elif modelname == 'Ridge':
            model = Ridge(alpha=1.0)
        elif modelname == 'ElasticNet':
            model = ElasticNet(alpha=1.0, l1_ratio=0.5)
        else:
            model = LinearRegression()
        # model = LinearRegression()
        # model = KNeighborsRegressor()
        # model = RandomForestRegressor()
        # model = DecisionTreeRegressor()
        X = df_head.drop(labels=['distance', 'y1', 'y2'], axis=1, inplace=False).values
        y = df_head[['y1', 'y2']].values
        X_input = input_X.values
        model.fit(X, y)
        y_pred = model.predict(X_input)
        print(y_pred)
        y1_pred = y_pred[0][0]
        y2_pred = y_pred[0][1]
        print(y1_pred)
        print(y2_pred)
        pred_jiaobi = y1_pred / jiaotan_s
        pred_meibi = y2_pred / penchuimei_s
        print(pred_jiaobi)
        print(pred_meibi)
        return pred_jiaobi, pred_meibi